package com.dave.gd.core.base.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import com.dave.gd.common.log.ILogger;
import com.dave.gd.common.log.LoggerFactory;
import com.dave.gd.common.util.Constants;
import com.dave.gd.common.util.NumberUtils;
import com.dave.gd.common.util.spring.ApplicationContextUtil;
import com.dave.gd.core.base.dao.BaseDao;
import com.dave.gd.core.common.pagehelper.PageInfo;
import com.dave.gd.core.util.SqlDealUtil;
import com.dave.gd.core.util.sqlparse.Dialect;
import com.dave.gd.core.util.sqlparse.Parse;
import com.dave.gd.core.util.sqlparse.impl.SimplerParse;

@Service("baseDao")
public class BaseDaoImpl implements BaseDao {
    private static final ILogger LOGGER = LoggerFactory.getLogger(BaseDaoImpl.class);
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private static final Parse PARSE = SimplerParse.getParse(Dialect.mysql);
    
    /**
     * 获取JdbcTemplate对象。
     * 
     * @return JdbcTemplate对象
     */
    public JdbcTemplate getJdbcTemplate() {
        if (this.jdbcTemplate == null) {
            this.jdbcTemplate = ApplicationContextUtil.getBean("jdbcTemplate");
        }
        return this.jdbcTemplate;
    }
    
    private static ThreadLocal<Integer> first = new ThreadLocal<Integer>();
    
    private static ThreadLocal<Integer> last = new ThreadLocal<Integer>();
    
    @Override
    public void setFirst(int first) {
        BaseDaoImpl.first.set(first);
    }
    
    @Override
    public int getFirst() {
        return NumberUtils.nullToZero(BaseDaoImpl.first.get());
    }
    
    @Override
    public void setLast(int last) {
        BaseDaoImpl.last.set(last);
    }
    
    @Override
    public int getLast() {
        return NumberUtils.nullToZero(BaseDaoImpl.last.get());
    }
    
    /**
     * 重置first和last属性。
     */
    protected void resetLimits() {
        setFirst(0);
        setLast(0);
    }
    
    @Override
    public void jdbcExecute(final String sql) {
        this.getJdbcTemplate().execute(sql);
    }
    
    @Override
    public int jdbcQueryForInt(final String sql, final Object... args) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, args);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
    
    @Override
    public int jdbcQueryForInt(final String sql, final Object[] args, final int[] argTypes) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, args, argTypes);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
    
    @Override
    public int jdbcQueryForInt(final String sql) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql);
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
    
    @Override
    public <E> List<E> jdbcQueryForList(final String sql, final Class<E> elementType,
        final Object... args) {
        return this.getJdbcTemplate().queryForList(sql, elementType, args);
    }
    
    @Override
    public <E> List<E> jdbcQueryForList(final String sql, final Class<E> elementType) {
        return this.getJdbcTemplate().queryForList(sql, elementType);
    }
    
    @Override
    public List<Map<String, Object>> jdbcQueryForList(final String sql, final Object... args) {
        return this.getJdbcTemplate().queryForList(sql, args);
    }
    
    @Override
    public <E> List<E> jdbcQueryForList(final String sql, final Object[] args,
        final Class<E> elementType) {
        return this.getJdbcTemplate().queryForList(sql, args, elementType);
    }
    
    @Override
    public <E> List<E> jdbcQueryForList(final String sql, final Object[] args,
        final int[] argTypes, final Class<E> elementType) {
        return this.getJdbcTemplate().queryForList(sql, args, argTypes, elementType);
    }
    
    @Override
    public List<Map<String, Object>> jdbcQueryForList(final String sql, final Object[] args,
        final int[] argTypes) {
        return this.getJdbcTemplate().queryForList(sql, args, argTypes);
    }
    
    @Override
    public List<Map<String, Object>> jdbcQueryForList(final String sql) {
        return this.getJdbcTemplate().queryForList(sql);
    }
    
    @Override
    public <E> List<E> jdbcFindList(final String sql, final Class<E> elementType) {
        return this.jdbcFindList(sql, elementType, null);
    }
    
    @Override
    public <E> List<E> jdbcFindList(final String sql, final Class<E> elementType,
        final List<Object> params) {
        Object[] array = null;
        if (params != null) {
            array = new Object[params.size()];
            params.toArray(array);
        }
        return this.executeJdbc(sql, elementType, array);
    }
    
    @Override
    public int jdbcGetSize(final String sql, final List<Object> params) {
        //        final String totalCountSql = getTotalCountSql(sql);
        final String totalCountSql = BaseDaoImpl.PARSE.getCountSql(sql);
        
        Object[] array = null;
        if (params != null) {
            array = new Object[params.size()];
            params.toArray(array);
        }
        return this.jdbcQueryForInt(totalCountSql, array);
    }
    
    private String getTotalCountSql(final String sql) {
        StringBuilder buffer = new StringBuilder();
        String sqlString = sql.trim();
        final int iIndex = (" " + sqlString.toLowerCase()).indexOf(" from ");
        String sqlHeaderString = sqlString.substring(0, iIndex);
        if (sqlHeaderString.toLowerCase().indexOf("distinct") == -1) {
            buffer.append("select count(*) ").append(sqlString.substring(iIndex));
        } else {
            buffer.append("select count(*) from (").append(sqlString).append(") ");
        }
        
        // 增加处理，将count语句的order by 去除
        String countSql = buffer.toString();
        countSql = SqlDealUtil.repOrderBy(countSql);
        
        return countSql;
    }
    
    @Override
    public <E> PageInfo<E> jdbcFindPageInfo(final String sql, final Class<E> elementType,
        final int currentPage, final int perPageNum) {
        return this.jdbcFindPageInfo(sql, elementType, null, currentPage, perPageNum);
    }
    
    @Override
    public <E> PageInfo<E> jdbcFindPageInfo(final String sql, final Class<E> elementType,
        final List<Object> params, final int currentPage, final int perPageNum) {
        int pageSize = perPageNum;
        int page = currentPage;
        if (pageSize == 0) {
            pageSize = Constants.DEFAULT_PAGE_SIZE;
        }
        if (currentPage == 0) {
            page = 1;
        }
        this.setFirst((page - 1) * pageSize);
        this.setLast(pageSize);
        
        Object[] array = null;
        //        if (params != null) {
        //            array = new Object[params.size()];
        //            params.toArray(array);
        //        }
        //构建分页
        String pageSql = PARSE.getPageSql(sql);
        List<Object> pageParams = null;
        if (params != null) {
            pageParams = new ArrayList<Object>(params);
        } else {
            pageParams = new ArrayList<Object>();
        }
        pageParams.addAll(PARSE.getPageParam(page, pageSize));
        if (pageParams != null) {
            array = new Object[pageParams.size()];
            pageParams.toArray(array);
        }
        
        final List<E> list = this.executeJdbc(pageSql, elementType, array);
        
        //查询总量
        final int totalCounts = this.jdbcGetSize(sql, params);
        int totalPages = totalCounts / pageSize;
        if ((totalCounts % pageSize) > 0) {
            totalPages += 1;
        }
        
        final PageInfo<E> pageInfo = new PageInfo<E>(list);
        pageInfo.setTotal(totalCounts);
        pageInfo.setPages(totalPages);
        pageInfo.setNavigatePages(totalPages);
        pageInfo.setPageNum(page);
        pageInfo.setPageSize(pageSize);
        //由于结果是>startRow的，所以实际的需要+1
        if (list.size() == 0) {
            pageInfo.setStartRow(0);
            pageInfo.setEndRow(0);
        } else {
            pageInfo.setStartRow(this.getFirst() + 1);
            pageInfo.setEndRow(this.getFirst() + list.size());
        }
        pageInfo.setList(list);
        pageInfo.calcAndJudge();
        this.resetLimits();
        
        return pageInfo;
    }
    
    @Override
    public long jdbcQueryForLong(final String sql, final Object... args) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, args);
        if (rs.next()) {
            return rs.getLong(1);
        }
        return 0;
    }
    
    @Override
    public long jdbcQueryForLong(final String sql, final Object[] args, final int[] argTypes) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql, args, argTypes);
        if (rs.next()) {
            return rs.getLong(1);
        }
        return 0;
    }
    
    @Override
    public long jdbcQueryForLong(final String sql) {
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql);
        if (rs.next()) {
            return rs.getLong(1);
        }
        return 0;
    }
    
    @Override
    public Map<String, Object> jdbcQueryForMap(final String sql, final Object... args) {
        return this.getJdbcTemplate().queryForMap(sql, args);
    }
    
    @Override
    public Map<String, Object> jdbcQueryForMap(final String sql, final Object[] args,
        final int[] argTypes) {
        return this.getJdbcTemplate().queryForMap(sql, args, argTypes);
    }
    
    @Override
    public Map<String, Object> jdbcQueryForMap(final String sql) {
        return this.getJdbcTemplate().queryForMap(sql);
    }
    
    @Override
    public <E> E jdbcQueryForObject(final String sql, final Class<E> requiredType,
        final Object... args) {
        return this.getJdbcTemplate().queryForObject(sql, requiredType, args);
    }
    
    @Override
    public <E> E jdbcQueryForObject(final String sql, final Class<E> requiredType) {
        return this.getJdbcTemplate().queryForObject(sql, requiredType);
    }
    
    @Override
    public <E> E jdbcQueryForObject(final String sql, final Object[] args,
        final Class<E> requiredType) {
        return this.getJdbcTemplate().queryForObject(sql, args, requiredType);
    }
    
    @Override
    public <E> E jdbcQueryForObject(final String sql, final Object[] args, final int[] argTypes,
        final Class<E> requiredType) {
        return this.getJdbcTemplate().queryForObject(sql, args, argTypes, requiredType);
    }
    
    @Override
    public SqlRowSet jdbcQueryForRowSet(final String sql, final Object... args) {
        return this.getJdbcTemplate().queryForRowSet(sql, args);
    }
    
    @Override
    public SqlRowSet jdbcQueryForRowSet(final String sql, final Object[] args, final int[] argTypes) {
        return this.getJdbcTemplate().queryForRowSet(sql, args, argTypes);
    }
    
    @Override
    public SqlRowSet jdbcQueryForRowSet(final String sql) {
        return this.getJdbcTemplate().queryForRowSet(sql);
    }
    
    @Override
    public int jdbcUpdate(final String sql, final Object... args) {
        return this.getJdbcTemplate().update(sql, args);
    }
    
    @Override
    public int jdbcUpdate(final String sql, final Object[] args, final int[] argTypes) {
        return this.getJdbcTemplate().update(sql, args, argTypes);
    }
    
    @Override
    public int jdbcUpdate(final String sql) {
        return this.getJdbcTemplate().update(sql);
    }
    
    /**
     * 执行JDBC查询语句，并获得数据列表。
     * 
     * @param <E>
     *            实体类型
     * @param sql
     *            SQL语句
     * @param elementType
     *            实体Class
     * @param params
     *            参数列表
     * @return 数据列表
     */
    private <E> List<E> executeJdbc(final String sql, final Class<E> elementType,
        final Object[] params) {
        final List<E> data = new ArrayList<E>();
        final RowMapper<E> rowMapper = ParameterizedBeanPropertyRowMapper.newInstance(elementType);
        this.getJdbcTemplate().query(sql, params, new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(final ResultSet rs) throws SQLException {
                final List<E> pageItems = data;
                //                int currentRow = 0;
                //                final int startRow = BaseDaoImpl.this.getFirst();
                //                int lastRow = startRow + BaseDaoImpl.this.getLast();
                //                while (rs.next() && (lastRow == 0 || currentRow < lastRow)) {
                //                    if (currentRow >= startRow) {
                //                        E e = rowMapper.mapRow(rs, currentRow);
                //                        //                        if (e instanceof AbstractBaseEntity) {
                //                        //                            CacheContext.setEnttToCache(e.getClass().getName(),
                //                        //                                ((AbstractBaseEntity) e).getId(), ((AbstractBaseEntity) e));
                //                        //                        }
                //                        pageItems.add(e);
                //                    }
                //                    currentRow++;
                //                    if (currentRow == Constants.DEFAULT_LIMIT_SIZE) {
                //                        LOGGER.info("===该查询语句结果集数量超过" + Constants.DEFAULT_LIMIT_SIZE + "条，语句为："
                //                            + sql);
                //                    }
                //                }
                int currentRow = 0;
                while (rs.next()) {
                    E e = rowMapper.mapRow(rs, currentRow);
                    pageItems.add(e);
                    currentRow++;
                    
                    if (currentRow == Constants.DEFAULT_LIMIT_SIZE) {
                        LOGGER.info("===该查询语句结果集数量超过" + Constants.DEFAULT_LIMIT_SIZE + "条，语句为："
                            + sql);
                    }
                }
                return data;
            }
        });
        return data;
    }
    
}
